package com.example.demo.mapper;

import com.example.demo.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.Date;
import java.util.List;

@Mapper
@Repository(value = "CustomerMapper")
public interface CustomerMapper {

    /**
     * 用户注册
     * @param customer
     */
    @Insert("insert into customer(  " +
            "phone,password,inviter,invitation_code  " +
            ")values(  " +
            "#{phone},#{password},#{inviter},#{invitationCode}" +
            ")")
     void addCustomer(Customer customer);

    /**
     * 注册的同时生成钱包
     * @param phone
     */
    @Insert("INSERT INTO wallet(phone,invitation_code)VALUES(#{phone},#{invitationCode})")
    @Options(useGeneratedKeys = false)
    void saveWallet(@Param("phone") String phone,@Param("invitationCode") String invitationCode);

    /**
     * 查询是否是重复账号
     * @param phone
     * @return
     */
    @Select("SELECT COUNT(*) FROM customer WHERE phone = #{phone}")
    Integer getCustomerNum(String phone);

    @Select("SELECT  COUNT(*) FROM customer WHERE invitation_code = #{inviter}")
    Integer getJudge(String inviter);

    /**
     * 用户登录
     * @param phone
     * @return
     */
    @Select("SELECT * FROM customer WHERE phone = #{phone}")
    Customer customerLogin(String phone);

    /**
     * 修改密码
     * @param phone
     * @param password
     */
    @Update("UPDATE customer SET password = #{password} " +
            "WHERE phone = #{phone}")
    void upPassword(@Param("phone")String phone,@Param("password") String password);


    /**
     * 判断是否认证会员
     * @param phone
     * @return
     */
    @Select("SELECT member FROM customer WHERE phone = #{phone}")
    Integer judgeState(String phone);

    /**
     * 开通会员开始工作
     * @return
     */
    @Insert("INSERT INTO work_table1 (\n" +
            "table_code,phone,startTime\n" +
            ")VALUES(\n" +
            "#{tableCode},#{phone},#{startTime}\n" +
            ")")
    void  startMemberWork(WorkTable workTable);


    @Insert("INSERT INTO work_table2 (\n" +
            "table_code,phone,start_time,update_time\n" +
            ")VALUES(\n" +
            "#{tableCode},#{phone},#{startTime},#{updateTime}\n" +
            ")")
    void startMemberWorkSubtabulation(WorkTable workTable);
    @Update("UPDATE work_table2 SET\n" +
            "update_time = #{updateTime},\n" +
            "state = 0\n" +
            "WHERE table_code = #{tableCode}")
    void startMemberWorkSubtabulationw(WorkTable workTable);


    /**
     * 没开通会员开始工作
     * @param workTable
     * @return
     */
    @Insert("INSERT INTO no_member_work1 (\n" +
            "table_code,phone,startTime\n" +
            ")VALUES(\n" +
            "#{tableCode},#{phone},#{startTime}\n" +
            ")")
    void  startNoMemberWork(WorkTable workTable);
    @Insert("INSERT INTO no_member_work2 (\n" +
            "table_code,phone,startTime,update_time\n" +
            ")VALUES(\n" +
            "#{tableCode},#{phone},#{startTime},#{updateTime}\n" +
            ")")
    void  startNoMemberWork2(WorkTable workTable);

    @Update("UPDATE no_member_work2 SET\n" +
            "update_time = #{updateTime},\n" +
            "state = 0\n" +
            "WHERE table_code = #{tableCode}")
    void startNoMemberWorkSubtabulation(WorkTable workTable);
    @Insert("INSERT INTO no_member_work2 (\n" +
            "table_code,phone,startTime,update_time\n" +
            ")VALUES(\n" +
            "#{tableCode},#{phone},#{startTime},#{updateTime}\n" +
            ")")
    void startNoMemberWorkSubtabulationw(WorkTable workTable);

    /**
     * 更新工作状态
     * @param workTable
     */
    @Update("UPDATE work_table2 SET allTime = allTime+#{allTime} ,earnMoney = earnMoney + #{earnMoney} ,update_time = #{updateTime}" +
            "WHERE phone = #{phone}  AND state = 0")
    void updateMemberWork(WorkTable workTable);
    @Update("UPDATE work_table2 SET allTime = allTime+#{allTime} ,earnMoney = #{earnMoney} ,update_time = #{updateTime}" +
            "WHERE phone = #{phone}  AND state = 0")
    void updateMemberWorkNormal(WorkTable workTable);



    @Update("UPDATE no_member_work2 SET allTime = allTime+#{allTime} ,earnMoney = #{earnMoney} ,update_time = #{updateTime}" +
            "WHERE phone = #{phone}  AND state = 0")
    void updateNoMemberWorkNormal(WorkTable workTable);



    @Update("UPDATE no_member_work2 SET allTime = #{allTime} ,earnMoney = #{earnMoney} ,update_time = #{updateTime}" +
            "WHERE phone = #{phone}  AND state = 0")
    void updateNoMemberWorkNorm(WorkTable workTable);
    @Update("UPDATE no_member_work1 SET allTime = allTime+#{allTime} ,earnMoney = #{earnMoney} " +
            "WHERE phone = #{phone}  AND state = 0")
    void updateNoMemberWorkNorma3(WorkTable workTable);

    @Select("SELECT   start_time  FROM work_table2 \n" +
            "WHERE phone = #{phone}  AND state = 0")
    Date getStartTime(String phone);
    @Select("SELECT   update_time  FROM work_table2 \n" +
            "WHERE phone = #{phone}  AND state = 0")
    Date getUpdateTime(String phone);
    @Select("SELECT   update_time  FROM no_member_work2 \n" +
            " WHERE phone = #{phone}  AND state = 0")
    Date getUpdateTimek(String phone);

    @Select("SELECT   update_time  FROM no_member_work2 \n" +
            " WHERE phone = #{phone} ")
    Date getUpdateTimeend(String phone);
    @Select("SELECT   update_time  FROM no_member_work2 \n" +
            "WHERE phone = #{phone} ")
    Date getNoMemberStartTime(String phone);
    @Select("SELECT   update_time  FROM no_member_work2 \n" +
            "WHERE phone = #{phone}  AND state = 0")
    Date getNoMemberUpdateTime(String phone);
    @Update("UPDATE no_member_work2 SET allTime = allTime+#{allTime} ,earnMoney = earnMoney + #{earnMoney} ,update_time=#{updateTime}" +
            "WHERE phone = #{phone}  AND state = 0")
    void updateNoMemberWork(WorkTable workTable);


    @Update("UPDATE no_member_work2 SET allTime = #{updateAfterDate} ,earnMoney = #{afterUpAmount} ,update_time=#{endDate}" +
            "WHERE phone = #{phone}  AND state = 0")
    void updateNoMemberWorklog(WorkLogs workLogs);

    @Update("UPDATE no_member_work1 SET allTime = allTime+#{allTime} ,earnMoney = earnMoney + #{earnMoney} " +
            "WHERE phone = #{phone}  AND state = 0")
    void updateNoMemberWorkMain(WorkTable workTable);

    @Update("UPDATE no_member_work2 SET allTime = #{allTime} ,earnMoney =  #{earnMoney}  " +
            "WHERE table_code = #{tableCode}")
    void updateNoMemberWork2(WorkTable workTable);

    @Select("SELECT update_time FROM work_table2 WHERE phone = #{phone} and  state = 0")
    Date getUpTime(String phone);

    @Select("SELECT allTime FROM no_member_work2 WHERE phone = #{phone} and state = 0 ")
    Integer getUseTime(String phone);

    @Select("SELECT earnMoney FROM no_member_work2 WHERE phone = #{phone} and state = 0 ")
    Integer getEarnMoney(String phone);
    @Select("SELECT allTime FROM no_member_work2 WHERE phone = #{phone} ")
    Integer getUseTimejudge(String phone);

    @Select("SELECT run_time FROM run_table WHERE phone = #{phone} order by start_time DESC limit 1")
    Integer getUseTime2(String phone);

    @Select("SELECT earn_money FROM run_table WHERE phone = #{phone} order by start_time DESC limit 1")
    Integer AllMoney(String phone);


    @Select("SELECT update_time FROM no_member_work2 WHERE phone = #{phone}")
    Date getNoMemeberUpTime(String phone);
    @Select("SELECT allTime FROM no_member_work2 WHERE phone = #{phone}")
    Integer getNoMemeberStartTime(String phone);


    /**
     * 结束工作
     * @param workTable
     */
    @Update("UPDATE work_table2 SET allTime =  allTime + #{allTime} , state = 1 " +
            " WHERE phone = #{phone} and state = 0 ")
    Integer endMemberWork1(WorkTable workTable);
    @Update("UPDATE work_table1 SET allTime =  #{allTime} ,earnMoney = #{earnMoney} " +
            " WHERE phone = #{phone} and state = 0")
    Integer endMemberWork2(WorkTable workTable);

    @Update("UPDATE work_table1 SET allTime =  #{allTime} ,earnMoney = #{earnMoney} , state = 1 " +
            " WHERE phone = #{phone} and state = 0")
    Integer endMemberWorkEnd(WorkTable workTable);


    @Update("UPDATE work_table1 SET allTime =  #{allTime} ,earnMoney = #{earnMoney} , state = 1 " +
            " WHERE phone = #{phone} and state = 0")
    void endAllWork(WorkTable workTable);

    @Update("UPDATE no_member_work1 SET allTime =  #{allTime} ,earnMoney = #{earnMoney} , state = 1 " +
            " WHERE phone = #{phone} and state = 0")
    void endAllWorkNoMember(WorkTable workTable);


    @Update("UPDATE no_member_work2 SET allTime =  #{allTime} ,earnMoney = earnMoney + #{earnMoney}  " +
            " WHERE phone = #{phone} and state = 0")
    Integer  endNoMemeberWork2(WorkTable workTable);

    @Update("UPDATE no_member_work2 SET state = 1  " +
            " WHERE phone = #{phone} and state = 0")
    Integer  endNoMemeberWorka(String phone);

    @Update("UPDATE no_member_work1 SET allTime =  #{allTime} ,earnMoney = #{earnMoney}  " +
            " WHERE phone = #{phone} and state = 0")
    Integer  endNoMemeberWork4(WorkTable workTable);


    @Update("UPDATE work_table2 SET state = 1" +
            " WHERE phone = #{phone} and state = 0")
    Integer endMemberWork3(WorkTable workTable);
    @Select("SELECT * FROM work_table2 WHERE phone = #{phone} and state = 0")
    WorkTable getWorkTable(String phone);

    @Select("SELECT * FROM no_member_work2 WHERE phone = #{phone}  ")
    WorkTable getWorkTableNoMembers(String phone);


    @Select("SELECT * FROM no_member_work2 WHERE phone = #{phone} and state = 0")
    WorkTable getNoMemeberWorkTable(String phone);


    @Select("SELECT * FROM work_table1 WHERE phone = #{phone} and state = 0")
    WorkTable getWorkTable1(String phone);

    /**
     * 查询附表中没有结束的订单的所有信息
     * @param phone
     * @return
     */
    @Select("SELECT * FROM work_table2 WHERE phone = #{phone} and state = 0")
    WorkTable getWorkTableIng(String phone);

    /**
     * 查询已用的工作时间（或剩余的工作时间）
     * @param code
     * @return
     */
    @Select("SELECT SUM(allTime) FROM work_table2 WHERE table_code = #{code}")
    Integer getUserTime(String code);


    @Select("SELECT * FROM no_member_work2 WHERE phone = #{phone} and state = 0")
    WorkTable getNoMemeberWorkTableIng(String phone);
    @Select("SELECT * FROM work_table2 WHERE table_code = #{code}")
    WorkTable getWorkTableEnd(String code);


    @Select("SELECT * FROM no_member_work2 WHERE table_code = #{code}")
    WorkTable getNoMemeberWorkTableEnd(String code);

    /**
     * 查询上个订单的结束时间
     * @param phone
     * @return
     */
    @Select("select   end_time from work_table1 \n" +
            "where phone = #{phone}  and state = 1\n" +
            "order by startTime \n" +
            "limit 1")
    Date getEndTime(String phone);

    /**
     * 没有认证结束工作
     * @param workTable
     */
    @Update("UPDATE no_member_work2 SET allTime =  #{allTime},earnMoney = earnMoney+#{earnMoney} ,state = 1 " +
            " WHERE phone = #{phone} and state = 0 ")
    Integer endNoMemberWork1(WorkTable workTable);
    @Update("UPDATE no_member_work2 SET allTime =  #{allTime}  ,state = 1 " +
            " WHERE phone = #{phone} and state = 0 ")
    Integer endNoMemberWorkm(WorkTable workTable);
    @Update("UPDATE no_member_work2 SET state = 1 " +
            " WHERE phone = #{phone} and state = 0")
    Integer endNoMemberWork2(WorkTable workTable);

    @Update("UPDATE no_member_work1 SET allTime =  #{allTime} ,earnMoney =  #{earnMoney} " +
            " WHERE phone = #{phone} and state = 0")
    Integer endNoMemberWork4(WorkTable workTable);
    @Update("UPDATE no_member_work1 SET allTime =  #{allTime} ,earnMoney =  #{earnMoney}" +
            " WHERE phone = #{phone} and state = 0")
    Integer endNoMemberWork3(WorkTable workTable);
    @Select("SELECT *  FROM no_member_work2 WHERE phone = #{phone} and state = 0")
    WorkTable getSubtabulation(String phone);
    @Select("SELECT *  FROM no_member_work2 WHERE phone = #{phone}  ")
    WorkTable getSubtabulationk(String phone);

    /**
     * 查询是否存在有未完成20分钟工作时间的订单
     * @param phone
     * @return
     */
    @Select("SELECT * FROM no_member_work2 WHERE phone = #{phone}")
    WorkTable getSubtabulation1(String phone);

    /**
     * 查询是否有正在运行的订单
     * @param phone
     * @return
     */
    @Select("SELECT * FROM no_member_work2 WHERE phone = #{phone} and state = 0")
    WorkTable getSubtabulationIng(String phone);

    @Select("SELECT allTime FROM no_member_work2 WHERE phone =  #{phone}")
    Integer getTime(String phone);

    @Select("SELECT allTime FROM work_table1 WHERE phone =  #{phone}  order by startTime DESC limit 1")
    Integer getTimework1(String phone);

    @Select("UPDATE no_member_work2 SET state = 1 WHERE phone = #{phone}")
    void endWorkNoMember(String phone);

    @Select("UPDATE no_member_work2 SET state = 0,earnMoney =  earnMoney+#{earnMoney},allTime =  #{allTime}  WHERE phone = #{phone}")
    void endWorkNoMemberKw(WorkTable workTable);
    @Select("UPDATE  work_table2 SET state = 1  WHERE phone = #{phone}")
    void endWorkMember(@Param("phone") String phone );


    /**
     * 查询是否订单有已完成的订单
     * @param phone
     * @return
     */
    @Select("SELECT allTime FROM no_member_work2 WHERE phone = #{phone} ")
    Integer getOverOrder(String phone);

    @Select("SELECT earnMoney FROM no_member_work2 WHERE phone = #{phone} ")
    Integer getWorkMoney(String phone);
    /**
     * 查询当前一共做了多久和订单号码
     * @param phone
     * @return
     */
    @Select("SELECT table_code,allTime FROM work_table1 WHERE state = 0 AND phone = #{phone}")
    WorkTable getTimeCode(String phone);

    /*** //当前总金额
     * @param phone
     * @return
     */
    @Select("select money from wallet   " +
            "    where phone = #{phone}")
    Integer getNowAllMoney(String phone);

    /**
     * //累计盈利
     * @param phone
     * @return
     */
    @Select("select sum(earn) from statistics where phone = #{phone}")
    Integer getAllMoney(String phone);

    /**
     * //昨日盈利
     * @param phone
     * @return
     */
    @Select("SELECT SUM(earn) FROM statistics WHERE phone = #{phone}\n" +
            "AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) =  DATE_FORMAT(start_time,'%Y-%m-%d')")
    Integer getYesMoney(String phone);

    /**
     * //一级好友盈利
     * @param phone
     * @return
     */
    @Select("SELECT  CASE WHEN amount IS NULL THEN '0' ELSE amount END AS  amount\n" +
            "FROM  ( SELECT SUM(earn)  AS amount FROM  statistics WHERE phone IN (\n" +
            "  SELECT  phone FROM customer WHERE inviter = #{inv} AND member=1)  )A")
    Integer getFirMoney(String inv);


    /**
     * //二级好友盈利
     * @param phone
     * @return
     */
    @Select("SELECT  CASE WHEN amount IS NULL THEN '0' ELSE amount END AS  amount \n" +
            "FROM  ( \n" +
            "SELECT SUM(earn)  AS amount \n" +
            "FROM statistics  WHERE phone IN ( \n" +
            "SELECT  phone FROM customer  WHERE inviter IN (\n" +
            "SELECT  invitation_code FROM customer WHERE inviter = #{inv} AND member=1\n" +
            ") AND member=1))A")
    Integer getSecMoney(String inv);

    /**
     * //三级好友盈利
     * @param phone
     * @return
     */
    @Select("SELECT CASE WHEN amount IS NULL THEN '0' ELSE amount END AS  amount\n" +
            "FROM (SELECT SUM(earn)  AS amount FROM\n" +
            "statistics  WHERE phone IN (\n" +
            "SELECT  phone FROM customer WHERE inviter IN \n" +
            "(SELECT  invitation_code FROM customer WHERE inviter IN \n" +
            "(SELECT  invitation_code FROM customer WHERE inviter = #{inv} AND member=1)  AND member=1  ) \n" +
            "AND member=1 ))A")
    Integer getThrMoney(String inv);

    /**
     * 查询用户当前级别stLevel，和下一级别enLevel，这一级别已经要求多少人currentPeople，这一级别总共需要多少人needPeople
     * @param phone
     * @return
     */
    @Select("SELECT CASE WHEN currentPeople IS NULL THEN '0' ELSE currentPeople END AS currentPeople,needPeople,stLevel,enLevel\n" +
            "FROM(\n" +
            "SELECT A.friends_first-stPeople AS currentPeople, enPeople-stPeople AS needPeople ,stLevel,enLevel\n" +
            "FROM (\n" +
            "SELECT  A.friends_first , wa.`member_level` AS stLevel,le.people AS stPeople,lev.`level` AS enLevel,lev.`people` AS enPeople\n" +
            "FROM wallet wa\n" +
            "INNER JOIN LEVEL le ON le.`level` = wa.`member_level`\n" +
            "INNER JOIN LEVEL lev ON lev.`level` = wa.`member_level`+1\n" +
            "LEFT JOIN (\n" +
            "SELECT COUNT(*) AS friends_first,inviter\n" +
            "FROM customer \n" +
            "WHERE inviter = #{inv} and member=1\n" +
            ")A ON A.inviter = wa.invitation_code \n" +
            "WHERE wa.invitation_code = #{inv}  \n" +
            ")A\n" +
            ")A")
    LevelSchedule getProgressBar(String inv);

    /**
     * 查询一级好友有多少人
     * @param phone
     * @return
     */
    @Select("SELECT COUNT(*)\n" +
            "FROM customer \n" +
            "WHERE inviter = #{inv} and member=1")
    Integer getFirFriend(String inv);

    /**
     * 查询二级好友有多少人
     * @param phone
     * @return
     */
    @Select("SELECT COUNT(*)\n" +
            " FROM customer \n" +
            " INNER JOIN ( SELECT invitation_code FROM customer  WHERE inviter = #{inv} AND member=1\n" +
            " )A ON A.invitation_code = inviter AND member=1")
    Integer getSecFriend(String inv);

    /**
     * 查询三级好友有多少人
     * @param phone
     * @return
     */
    @Select("SELECT COUNT(*)\n" +
            "FROM customer cu\n" +
            "INNER JOIN (\n" +
            "\tSELECT cu.invitation_code\n" +
            "\tFROM customer cu\n" +
            "\tINNER JOIN (\n" +
            "\t\tSELECT invitation_code\n" +
            "\t\tFROM customer \n" +
            "\t\tWHERE inviter = #{inv} and member=1\n" +
            "\t)A ON A.invitation_code = cu.inviter and member=1\n" +
            ")A ON A.invitation_code =  cu.inviter and member=1")
    Integer getThrFriend(String inv);

    /**
     * 查询视频 推广和Q&A
     * @param materialType
     * @return
     */
    @Select("SELECT * FROM material WHERE material_type = #{materialType} ORDER BY material_showId")
    List<Material> getMaterial(String materialType);

    /**
     * 提现记录
     * @param phone
     * @return
     */
    @Select("SELECT * FROM LOGS WHERE phone = #{phone} AND logs_type = 1 AND is_sure = 1 " +
            "ORDER BY logs_time DESC")
    List<Logs> getWithdrawals(String phone);

    @Select("SELECT wechat FROM customer WHERE phone = #{phone}")
    String isBinding(String phone);

    /**
     * 当成功邀请好友时，增加钱包的金额
     * @param phone
     * @param money
     */
    @Update("UPDATE wallet SET \n" +
            "money = money +#{money} \n" +
            "WHERE phone = #{phone}")
    void updateAWallet(@Param("phone") String phone,@Param("money") Integer money);


    @Update("UPDATE wallet SET \n" +
            "money = money +#{actualAmount} \n" +
            "WHERE phone = #{phone}")
    void updateAWalletlogs(WorkLogs workLogs);

    @Update("UPDATE wallet SET \n" +
            "money =  #{money} \n" +
            "WHERE phone = #{phone}")
    void updateAWallet1(@Param("phone") String phone,@Param("money") Integer money);

    @Update("UPDATE wallet SET \n" +
            "money = money + #{money} \n" +
            "WHERE phone = #{phone}")
    void updateWallet(@Param("phone") String phone,@Param("money") Integer money);

    /**
     * 查询上一级好友是谁
     * @param phone
     * @return
     */
    @Select("SELECT inviter FROM customer WHERE phone = #{phone}")
    String selectBPhone(String phone);

    /**
     * 查询用户表是否存在该手机号码
     * @param phone
     * @return
     */
    @Select("SELECT COUNT(*) FROM customer WHERE phone = #{phone}")
    Integer selectNum(String phone);

    /**
     * 查询验证码表是否存在该手机号码
     * @param phone
     * @return
     */
    @Select("SELECT COUNT(*) FROM verification_code WHERE phone = #{phone}")
    Integer selectCode(String phone);

    /**
     * 将手机号和验证码存入验证码表
     * @param phone
     * @param verificationCode
     */
    @Insert("INSERT INTO verification_code(\n" +
            "phone,verification_code\n" +
            ")VALUES(\n" +
            "#{phone},#{verificationCode}\n" +
            ")")
    @Options(useGeneratedKeys = false)
    void saveVerificationCode(@Param("phone") String phone,@Param("verificationCode") String verificationCode);

    /**
     * 根据手机号更新验证码
     * @param phone
     * @param verificationCode
     */
    @Update("update  verification_code set\n" +
            "verification_code = #{verificationCode}\n" +
            "where phone = #{phone}")
    void updateCode(@Param("phone") String phone,@Param("verificationCode") String verificationCode);


    /**
     * 根据手机号查询验证码
     * @param phone
     * @return
     */
    @Select("select verification_code from verification_code where phone = #{phone}")
    String getVerificationCode(String phone);


    /**
     * 查询用户等级和钱包金额 判断是否可以转账
     * @param telephone
     * @return
     */
//    @Select("CALL cash_extraction_by_wechat(#{phone})")
    @Select("SELECT money,member_level,SUM(amount)-(SELECT CASE WHEN amount IS NULL THEN 0 ELSE SUM(amount) END AS a FROM LOGS WHERE\n" +
            "\tphone=#{telephone} AND logs_type = 1  and is_sure = 1 and is_member = 0) AS amount\n" +
            "\tFROM  wallet wa\n" +
            "\tINNER JOIN LEVEL le ON wa.`member_level` >= le.`level`\n" +
            "\tWHERE wa.`phone`= #{telephone}   FOR UPDATE")
    Wallet getWallet(String telephone);

    @Select("SELECT amount FROM LEVEL WHERE LEVEL = #{lev}")
    Integer getMoney(Integer lev);
    /**
     * 根据手机号码查询微信openid
     * @param phone
     * @return
     */
    @Select("SELECT wechat FROM customer WHERE phone = #{phone}")
    String getOpenId(String phone);

    @Select("SELECT wechat FROM customer WHERE wechat = #{wechat}")
    String getOpenIdbg(String wechat);

    /**
     * 将转账信息存入logs表
     * @param logs
     */
    @Insert("INSERT INTO LOGS (\n" +
            "logs_code,logs_time,phone,logs_content,amount,logs_type\n" +
            ")VALUES(\n" +
            "#{logsCode},#{logsTime},#{phone},#{logsContent},#{amount},#{logsType}" +
            ")")
    void saveLogs(Logs logs);

    @Insert("INSERT INTO LOGS (\n" +
            "logs_code,logs_time,phone,logs_content,amount,logs_type,is_member\n" +
            ")VALUES(\n" +
            "#{logsCode},#{logsTime},#{phone},#{logsContent},#{amount},#{logsType},1" +
            ")")
    void saveLogsNoMember(Logs logs);

    /**
     * 确认转账是否成功
     * @param logs
     */
    @Update("UPDATE LOGS SET \n" +
            "is_sure = #{isSure},\n" +
            "time_sure = #{timeSure},\n" +
            "false_back = #{falseBack}\n" +
            "WHERE logs_code = #{logsCode}")
    void updateLogs(Logs logs);


    /**
     * 已认证查询表一是否有未完成的工作
     * @param phone
     * @return
     */
    @Select("SELECT table_code FROM  work_table1 WHERE phone = #{phone} AND state = 0")
    String getTableCode(String phone);

    /**
     * 未认证查询表一是否有未完成的工作
     * @param phone
     * @return
     */
    @Select("SELECT table_code FROM  no_member_work2 WHERE phone = #{phone} ")
    String getNoMemberTableCode(String phone);


    /**
     * 存储openId
     * @param openId
     * @param phone
     */
    @Update("UPDATE customer SET\n" +
            "wechat = #{openId}\n" +
            "WHERE phone = #{phone}")
    void  saveOpenId(@Param("openId") String openId,@Param("phone") String phone);


    /**
     * 获取baseInfo
     * @return
     */
    @Select("select * from base_info ")
    BaseInfo getBaseInfo();

    /**
     * 获取当前用户的等级
     * @param phone
     * @return
     */
    @Select("select member_level \n" +
            "from customer  ct\n" +
            "inner join wallet wt on wt.`phone` = ct.`phone`\n" +
            "where ct.`phone` = #{phone}\n")
    Integer getLevelByPhone(String phone);

    @Select("SELECT SUM(amount) FROM LEVEL WHERE LEVEL<=#{level}")
    Integer getAllLevelMoney(Integer level);

    @Select("SELECT COUNT(*) FROM LOGS WHERE phone = #{phone} AND is_sure = 1")
    Integer getCashTime(String phone);

    @Select("SELECT SUM(amount) FROM LOGS WHERE phone =  #{phone} AND is_sure = 1")
    Integer getAllTimeMoney(String phone);

    @Select("SELECT amount,LEVEL FROM LEVEL WHERE LEVEL<=#{level} ORDER BY LEVEL DESC LIMIT 1 ")
    Level getLevelAndMoney(Integer level);

    /**
     * 认证会员
     * @param phone
     */
    @Update("UPDATE customer SET member = 1 WHERE phone = #{phone}")
    void updateMemberState(String phone);


    /**
     * 查询上级一二三级好友
     * @param phone
     * @return
     */
    @Select("SELECT  cu.phone \n" +
            " FROM customer cu\n" +
            " INNER JOIN(\n" +
            " SELECT inviter AS inviter2 , inviter1 \n" +
            " FROM customer cu\n" +
            " INNER JOIN(\n" +
            "SELECT c2.inviter AS inviter1\n" +
            "FROM customer  c1\n" +
            "INNER JOIN customer c2 ON c1.`inviter` = c2.`invitation_code`\n" +
            "WHERE c1.phone = #{phone}\n" +
            " )A ON A.inviter1 = cu.`invitation_code`\n" +
            " )A ON A.inviter2 = cu.`invitation_code`")
    String getFriends3(String phone);

    @Select("SELECT cu.phone  \n" +
            "FROM customer cu INNER JOIN(\n" +
            "SELECT c2.inviter AS inviter1\n" +
            "FROM customer  c1\n" +
            "INNER JOIN customer c2 ON c1.`inviter` = c2.`invitation_code`\n" +
            "WHERE c1.phone = #{phone}\n" +
            ")A ON A.inviter1 = cu.`invitation_code`")
    String getFriends2(String phone);

    @Select("SELECT c2.phone\n" +
            "FROM customer  c1\n" +
            "INNER JOIN customer c2 ON c1.`inviter` = c2.`invitation_code`\n" +
            "WHERE c1.phone = #{phone}")
    String getFriends1(String phone);


    /**
     * 奖金额分别放入相应的账户
     * @param amount
     * @param phone
     */
    @Update("update wallet set money = money + #{amount} where phone = #{phone}")
    void saveWalletMoney(@Param("amount") Integer amount,@Param("phone") String phone);


    @Update("INSERT INTO statistics(\n" +
            "start_time,phone,earn,state\n" +
            ")VALUES(\n" +
            "#{startTime},#{phone},#{amount},1\n" +
            ")")
    @Options(useGeneratedKeys = false)
    void saveInvitationLogs(@Param("startTime") String startTime,@Param("amount") Integer amount,@Param("phone") String phone);
    /**
     * 查询当前对应的会员等级
     * @return
     */
    @Select("SELECT LEVEL FROM LEVEL WHERE people<=(\n" +
            "SELECT COUNT(*) FROM customer WHERE inviter = #{inv} and member = 1 \n" +
            ")\n" +
            "ORDER BY LEVEL DESC LIMIT 1 ")
    Integer getLevelNow(String inv);

    /**
     * 更新会员等级
     * @param phone
     * @param levelNow
     */
    @Update("UPDATE wallet SET member_level = #{levelNow} \n" +
            "WHERE phone = #{phone}")
    void updateCustomerLevel(@Param("phone") String phone,@Param("levelNow") Integer levelNow);

    /**
     * 根据手机号码查询是否是会员
     * @param phone
     * @return
     */
    @Select("SELECT member FROM customer WHERE phone = #{phone} ")
    Integer getMember(String phone);

    /**
     * 根据手机号码查询会员等级
     * @param phone
     * @return
     */
    @Select("SELECT member_level FROM wallet WHERE phone = #{phone} ")
    Integer getGrade(String phone);

    /**
     * 查询是否申请过退款
     * @param phone
     * @return
     */
    @Select("SELECT count(*) FROM apply_refund WHERE phone = #{phone} AND state =0")
    Integer getPhone(String phone);

    @Select("SELECT count(*) FROM apply_refund WHERE phone = #{phone} AND state =1")
    Integer getPhone2(String phone);

    /**
     * 提交申请退款
     * @param phone
     * @param grade
     * @param dateTime
     */
    @Insert("INSERT  INTO apply_refund(\n" +
            "phone,member_level,create_date\n" +
            ")VALUES(\n" +
            "#{phone},#{num},#{dateTime}" +
            ")")
    @Options(useGeneratedKeys = false)
    void saveApply(@Param("phone")String phone,@Param("num")Integer grade,@Param("dateTime")String dateTime);


    /**
     * 轮循通知
     * @return
     */
    @Select("SELECT  phone ,amount  FROM article " +
            "LIMIT 400")
    List<Logs> circularNotification();

    @Select("SELECT SUM(allTime) FROM work_table2 WHERE table_code = #{code}")
    Integer getSumTime(String code);

    @Select("SELECT SUM(allTime) FROM work_table2 WHERE table_code = (SELECT table_code FROM work_table2 WHERE phone = #{phone} AND state = 0)")
    Integer getSumTimeUp(String phone);


    @Insert("INSERT INTO statistics (\n" +
            " start_time,phone\n" +
            ")VALUES(\n" +
            " #{startTime},#{phone}" +
            ")")
    void saveWorkLogs(WorkTable workTable);

    @Update("UPDATE statistics SET state = 1 WHERE phone = #{phone}")
    void endAllLogsWork(String phone);


    @Update("UPDATE statistics SET earn = earn + #{earnMoney},time_difference= #{time}  WHERE phone = #{phone} AND state = 0 ")
    void updateWorkLogsAgain(WorkTable workTable);

    @Update("UPDATE statistics SET earn = earn + #{actualAmount},time_difference= #{workDate}  WHERE phone = #{phone} AND state = 0 ")
    void updateWorkNoLogsAgain(WorkLogs workLogs);

    @Update("UPDATE statistics SET state = 1 ,end_time = NOW() ,time_difference = #{terface} WHERE phone = #{phone} AND state = 0")
    void endStatic(@Param("phone") String phone,@Param("terface") Long terface);

    /**
     * 记录工作信息
     * @param workTable
     */
    @Update("UPDATE statistics SET earn = earn + #{earnMoney} WHERE phone = #{phone} AND state = 0")
    void updateWorkLogs(WorkTable workTable);

    @Update("UPDATE statistics SET earn = earn + #{actualAmount} WHERE phone = #{phone} AND state = 0")
    void updateWorkNoLogs(WorkLogs workLogs);

    @Update("UPDATE statistics SET earn = earn + #{earnMoney} and state = 1 WHERE phone = #{phone} AND state = 0")
    void updateWorkLogsll(WorkTable workTable);

    @Update("UPDATE statistics SET earn =  #{earnMoney} and state = 1 WHERE phone = #{phone} AND state = 0")
    void updateWorkLogsl(WorkTable workTable);

    @Update("UPDATE statistics SET earn = earn + #{earnMoney} , state = 1 WHERE phone = #{phone} AND state = 0")
    void updateWorkLogs2l(WorkTable workTable);

    /**
     * 查询域名
     * @return
     */
    @Select("SELECT url_name FROM base_info ")
    String getUrl();

    @Update("UPDATE wallet SET state = 0 WHERE phone = #{phone}")
    void update(String phone);



    @Select("select count(*) from no_member_work2 where phone = #{phone} and state = 0")
    Integer endIs(String phone);

    @Select("select count(*) from run_table where phone = #{phone} and state = 0")
    Integer endIsNo(String phone);


    @Select("select frozen from customer where phone=#{phone}")
    Integer getFrozen(String phone);

    @Select("UPDATE customer SET frozen = 1  where phone=#{phone}")
    Integer setFrozen(String phone);


    /**
     * 通过手机号获取分享码
     * @param phone
     * @return
     */
    @Select("SELECT invitation_code FROM customer WHERE phone = #{phone}")
    String getInv(String phone);

    @Select("SELECT SUM(amount) FROM LOGS WHERE phone = #{phone} \n" +
            "AND logs_type = 1 AND is_sure = 1")
    Integer getReadyAmount(String phone);

    @Select("SELECT time_sure FROM LOGS WHERE phone = #{phone} AND logs_type = 2 AND is_sure = 1")
    Date getVipTime(String phone);

    @Select("SELECT amount FROM accept_amount where create_date> DATE_SUB(NOW(),INTERVAL  1 HOUR)")
    List<Double> getAmount();

    @Select("SELECT amount FROM accept_amount WHERE  phone = #{phone} AND  create_date> DATE_SUB(NOW(),INTERVAL  1 HOUR)")
    Double getAmountNum(String phone);

    @Select("SELECT SUM(indexs) FROM accept_amount WHERE phone = #{phone}  AND YEAR(create_date)=YEAR(NOW())" +
            " AND MONTH(create_date)=MONTH(NOW()) AND DAY(create_date)=DAY(NOW())")
    Integer getEveryDay(String phone);

    @Insert("INSERT INTO accept_amount (\n" +
            "phone,amount,create_date\n" +
            ")VALUES(\n" +
            "#{phone},#{amount},#{date}" +
            ")")
    @Options(useGeneratedKeys = false)
    void saveAmount(@Param("phone") String phone,@Param("amount") Double amount,@Param("date") String date);


    @Update("UPDATE accept_amount SET is_pay = 1,indexs = indexs+1 ,is_warning = 0 WHERE phone = #{phone} ORDER BY create_date DESC LIMIT 1")
    void updateNotice(String phone);

    @Select("SELECT weChat_url FROM base_info ")
    String selUrlWechat();
}
